﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace FluentETL.Impl
{
    public class OleDbDestination : OleDbBase, IDestination
    {
        private OleDbConnection _activeConn = null;
        private bool _deleteFirst;
        protected Action<OleDbConnection> DoPreTransform { get; set; }
        protected Action<OleDbConnection> DoPostTransform { get; set; }
        protected Action<OleDbConnection, IRow> DoInsert { get; set; }
        protected Action<OleDbConnection, IRow> DoUpdate { get; set; }
        protected Action<OleDbConnection, IRow> DoDelete { get; set; }
        protected Func<IRow, RowOperation> GetRowOperation { get; set; }

        public OleDbDestination()
        {
            this.GetRowOperation = x => RowOperation.Process;
        }

        public OleDbDestination ToTable(string tableName)
        {
            this.CommandText = tableName;
            this.CommandType = CommandType.TableDirect;
            OleDbCommand cmd = null;
            this.InsertWithCommand((conn, row) =>
            {
                if (cmd == null)
                {
                    StringBuilder sqlInsert = new StringBuilder();
                    string[] destColNames = (from c in row.Fields
                                             select c).ToArray();
                    sqlInsert.Append("INSERT INTO " + (this.CommandText) + " (");
                    sqlInsert.Append(string.Join(",", destColNames));
                    sqlInsert.Append(") VALUES (");
                    string[] destColParamNames = (from c in destColNames
                                                  select "@" + c).ToArray();
                    sqlInsert.Append(string.Join(",", destColParamNames));
                    sqlInsert.Append(")");

                    cmd = conn.CreateCommand();
                    cmd.CommandText = sqlInsert.ToString();
                }
                else
                    cmd.Parameters.Clear();
                foreach (var field in row.Fields)
                {
                    object fieldValue = row[field];
                    cmd.Parameters.AddWithValue(field, fieldValue ?? DBNull.Value);
                }
                return cmd;
            });
            return this;
        }

        public OleDbDestination Connection(string connString)
        {
            this.ConnectionString = connString;
            return this;
        }

        public OleDbDestination Connection(OleDbConnection conn)
        {
            this.ConnectionObject = conn;
            return this;
        }

        public OleDbDestination PreTransform(Action<OleDbConnection> action)
        {
            this.DoPreTransform = action;
            return this;
        }

        public OleDbDestination PostTransform(Action<OleDbConnection> action)
        {
            this.DoPostTransform = action;
            return this;
        }

        public OleDbDestination InsertWithCommand(Func<OleDbConnection, IRow, OleDbCommand> func)
        {
            return InsertWithAction((conn, row) => { func(conn, row).ExecuteNonQuery(); });
        }

        public OleDbDestination UpdateWithCommand(Func<OleDbConnection, IRow, OleDbCommand> func)
        {
            return UpdateWithAction((conn, row) => { func(conn, row).ExecuteNonQuery(); });
        }

        public OleDbDestination DeleteWithCommand(Func<OleDbConnection, IRow, OleDbCommand> func)
        {
            return DeleteWithAction((conn, row) => { func(conn, row).ExecuteNonQuery(); });
        }

        public OleDbDestination InsertWithAction(Action<OleDbConnection, IRow> action)
        {
            this.DoInsert = action;
            return this;
        }

        public OleDbDestination UpdateWithAction(Action<OleDbConnection, IRow> action)
        {
            this.DoUpdate = action;
            return this;
        }

        public OleDbDestination DeleteWithAction(Action<OleDbConnection, IRow> action)
        {
            this.DoDelete = action;
            return this;
        }

        public OleDbDestination DiscriminateOperation(Func<IRow, RowOperation> func)
        {
            this.GetRowOperation = func;
            return this;
        }

        public OleDbDestination DeleteFirst()
        {
            _deleteFirst = true;
            return this;
        }

        void IDestination.InTransformContext(ITransform t, Action action)
        {
            base.WithConnection(conn =>
            {
                _activeConn = conn;

                if (this.DoPreTransform != null)
                    this.DoPreTransform(conn);
                if (this.CommandType == CommandType.TableDirect)
                {
                    EnsureMissingOperationsForTable(t, conn);
                }
                if (_deleteFirst && this.CommandType == System.Data.CommandType.TableDirect)
                {
                    OleDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE FROM " + (this.CommandText);
                    cmd.ExecuteNonQuery();
                }
                action();
                if (this.DoPostTransform != null)
                    this.DoPostTransform(conn);
            });
        }

        #region IDestination Members

        void IDestination.Process(IRow row)
        {
            RowOperation rowOp = this.GetRowOperation(row);
            switch (rowOp)
            {
                case RowOperation.Process:
                case RowOperation.Insert:
                    this.DoInsert(_activeConn, row);
                    break;
                case RowOperation.Update:
                    this.DoUpdate(_activeConn, row);
                    break;
                case RowOperation.Delete:
                    this.DoDelete(_activeConn, row);
                    break;
            }
        }

        void IDestination.Insert(IRow row)
        {
            this.DoInsert(_activeConn, row);
        }

        void IDestination.Update(IRow row)
        {
            this.DoUpdate(_activeConn, row);
        }

        void IDestination.Delete(IRow row)
        {
            this.DoDelete(_activeConn, row);
        }

        #endregion

        private void EnsureMissingOperationsForTable(ITransform t, OleDbConnection conn)
        {
            if (this.DoUpdate == null || this.DoDelete == null)
            {
                IList<string> pkFields = DbUtil.GetOleDbPrimaryKeys(conn, this.CommandText);
                if (pkFields.Count > 0)
                {
                    if (this.DoUpdate == null)
                    {
                        IEnumerable<string> updateFields = from c in t.GetMappedDestinationFields()
                                                           where !pkFields.Contains(c)
                                                           select c;
                        StringBuilder sqlUpdate = new StringBuilder();
                        string[] setStmts = (from c in updateFields
                                             select (c) +
                                                "=" +
                                                "@" + c).ToArray();
                        string[] whereConds = (from c in pkFields
                                               select (c) +
                                                "=" +
                                                "@" + c).ToArray();
                        sqlUpdate.Append("UPDATE " + (this.CommandText) + " SET ");
                        sqlUpdate.Append(string.Join(",", setStmts));
                        sqlUpdate.Append(" WHERE ");
                        sqlUpdate.Append(string.Join(" AND ", whereConds));

                        OleDbCommand cmd = conn.CreateCommand();
                        cmd.CommandText = sqlUpdate.ToString();
                        this.UpdateWithCommand((conn2, row) =>
                        {
                            cmd.Parameters.Clear();
                            foreach (var field in updateFields)
                            {
                                object fieldValue = row[field];
                                cmd.Parameters.AddWithValue(field, fieldValue ?? DBNull.Value);
                            }
                            foreach (var field in pkFields)
                            {
                                object fieldValue = row[field];
                                cmd.Parameters.AddWithValue(field, fieldValue ?? DBNull.Value);
                            }
                            return cmd;
                        });
                    }
                    if (this.DoDelete == null)
                    {
                        StringBuilder sqlUpdate = new StringBuilder();
                        string[] whereConditions = (from c in pkFields
                                                    select DbUtil.SqlServerEscapeTableOrColumnName(c) +
                                                        "=" +
                                                        "@" + c).ToArray();
                        sqlUpdate.Append("DELETE FROM " + DbUtil.SqlServerEscapeTableOrColumnName(this.CommandText));
                        sqlUpdate.Append(" WHERE ");
                        sqlUpdate.Append(string.Join(" AND ", whereConditions));

                        OleDbCommand cmd = conn.CreateCommand();
                        cmd.CommandText = sqlUpdate.ToString();
                        this.DeleteWithCommand((conn2, row) =>
                        {
                            cmd.Parameters.Clear();
                            foreach (var field in row.Fields)
                            {
                                object fieldValue = row[field];
                                cmd.Parameters.AddWithValue(field, fieldValue ?? DBNull.Value);
                            }
                            return cmd;
                        });
                    }
                }
            }
        }
    }
}
